<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<?php
	function get_connection() {
		$con = mysql_pconnect('147.175.187.53', 'root', 'qwerty123', 65536);
		if (!$con) {
			echo('<h3 style="color: Red;">Cannot connect to MySQL server &nbsp;&nbsp; :\'(</h3>');
			die('Could not connect: ' . mysql_error());
		}
		mysql_query("SET NAMES 'utf8'");
		mysql_select_db("study_department", $con);
		return $con;
	}
	
	function get_connection_stagingarea() {
		$con = mysql_pconnect('147.175.187.53', 'root', 'qwerty123', 65536);
		if (!$con) {
			echo('<h3 style="color: Red;">Cannot connect to MySQL server &nbsp;&nbsp; :\'(</h3>');
			die('Could not connect: ' . mysql_error());
		}
		mysql_query("SET NAMES 'utf8'");
		mysql_select_db("stagingarea", $con);
		return $con;
	}
	
	/*$connection = get_connection_stagingarea();
		
    $queryString = "INSERT INTO faculty (`FacultyKey`,`Name`,`Address`) VALUES (1,'Fakulta informatiky a informačných technológií','Ilkovičova 3') " ;
	$queryAnswer = mysql_query($queryString,$connection)or die(mysql_error()); 
				
	while($row = mysql_fetch_array($queryAnswer) ) {
		$text .= $row['ais_id'];
				
	}
	mysql_close($connection);*/
	
	//*************************************************************************************
	//naplnanie tabulky fieldofstudy
	/*$connection = get_connection();
		
    $queryString = "SELECT programme FROM `t_student` GROUP BY programme ORDER BY programme " ;
	$queryAnswer = mysql_query($queryString,$connection)or die(mysql_error()); 
	$connection = get_connection_stagingarea();			
	$index=0;
	$queryString2 = "TRUNCATE fieldofstudy" ;
	$queryAnswer2 = mysql_query($queryString2,$connection)or die(mysql_error()); 
	while($row = mysql_fetch_array($queryAnswer) ) {
		if ($row['programme']!=""){
			$index++;
    		$queryString2 = "INSERT INTO fieldofstudy (`FieldOfStudyKey`,`Name`) VALUES (".$index.",'".$row['programme']."') " ;
			$queryAnswer2 = mysql_query($queryString2,$connection) or die(mysql_error()); 
		}		
	
	}*/
	//**************************************************************************************
	//naplnenie tabulky student
	$connection = get_connection();
		
    $queryString = "SELECT p.ais_id, firstname.value AS firstname, surname.value AS surname,st.year FROM study_department.t_person p
					left join study_department.t_person_data firstname on firstname.person_id=p.id AND firstname.data_type_id=1 
					left join study_department.t_person_data surname on surname.person_id=p.id AND surname.data_type_id=2
					left join study_department.t_student st on st.person_id=p.id  
					WHERE person_type_id=1" ;
	$queryAnswer = mysql_query($queryString,$connection)or die(mysql_error()); 
	
	$connection = get_connection_stagingarea();			
	$index=0;
	$queryString2 = "TRUNCATE student" ;
	$queryAnswer2 = mysql_query($queryString2,$connection)or die(mysql_error()); 
	
	while($row = mysql_fetch_array($queryAnswer) ) {
		
			$index++;
    		$queryString2 = "INSERT INTO student (`StudentKey`,`AIS_ID`,`LibraryAccountKey`,`FieldOfStudyKey`,`StudyStatusKey`,`FirstName`,`LastName`,`Grade`) VALUES ( 	'".$index."','".$row['ais_id']."',0,0,0,'".$row['firstname']."','".$row['surname']."','".$row['year']."') " ; //treba dorobit LibraryAccountKey  FieldOfStudyKey  StudyStatusKey
			$queryAnswer2 = mysql_query($queryString2,$connection) or die(mysql_error()); 
			echo "1";
			
	
	}
	
	mysql_close($connection);
	
				
	/*$connection = get_connection();
		
      			$queryString = "SELECT * FROM t_person WHERE person_type_id=1" ;
				$queryAnswer = mysql_query($queryString,$connection)or die(mysql_error()); 
				
				while($row = mysql_fetch_array($queryAnswer) ) {
					$text .= $row['ais_id'];
				
				}
				mysql_close($connection);*/
				echo $text;
?>
</body>
</html>
